MS Excel & MS Word Without Interop Objects
See Using the Microsoft Office Library.
These functions apply to Word and Excel files that are in XML format, that is, from Office 2007, files with extensions docx and xlsx.
The export and import DataTable functions apply to files with extensions xlsx and csv.
For sample projects, see Using MS Excel & MS Word Without Interop.
MS Word Without INterop
Append Text to Word Document
Adds specified text at the end of a Word document. The text is added at the start of a line below the existing text.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
document name |
text |
The full path (including file name) of the Word file |
|
text |
text |
The text to append to the Word document |
Returns
Nothing
Example
This example appends the text Lorem ipsum dolor sit amet to the Word file at C:/Temp/SampleFile.docx.
The Word document is shown below:
Before:
After:
Get Document Text
Retrieves all text from a Word document.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
document name |
text |
The full path (including file name) of the Word file |
Returns
Returns the text of the Word document in text format
Example
This example retrieves the text in a word file at C:/Temp/SampleFile.docx, and stores it in the text variable text_response.
In debug mode, the variable text_response is set to the text in the Word document.
Replace Text in Word Document
Finds all instances of specified text within a Word document and replaces all instances with specified replacement text. This function is case sensitive.
The formatting of the replacement text is the same as that of the replaced text.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
document name |
text |
The full path (including file name) of the Word file |
|
find what |
text |
The text to find |
|
replace with |
text |
The replacement text |
Returns
Nothing
Example
This example replaces the word fox in the Word file at C:/Temp/SampleFile.docx with the word cat.
The Word document is shown below:
Before:
After:
MS Excel Without Interop
Example Files
An example is provided for each function listed below.
The sample project includes one workflow for each function.
Download the sample project here.
The sample Excel file, SampleFile.xlsx, must be placed at c:/temp for the example workflows to run.
Run the workflows from the Flows tab in the Monitor.
Append Rows in Excel
Adds rows with specified data from the first empty row of the specified sheet in an Excel file, and sets the data format of each column in the range populated.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
data format |
list of rows |
The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used. |
|
values |
list of rows |
The values to write to the cells in the range |
Returns
Returns a boolean value true if the operation was successful
Example
This example adds a single row to the file at c:/Temp/SampleFile.xlsx. The row is as follows:
| A | B | C | D | |
|
Format |
string |
number | string | datetime |
|
Data |
Payment Terms (Days): |
30 | Latest Date: | 2020-05-01 |
The workflow below populates two rows:
-
data_format_row_a: Holds the format values as per the table above. This row is added to the list of rows data_formats_a.
-
data_row_a: Holds the data values as per the table above. This row is added to the list of rows data_a.
The rows are then appended to the Excel file.
After execution, the row is appended to the Excel file.
Clear Excel Rows
Clears the contents of the specified range of rows from the specified sheet in the specified Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
start row |
number |
The number of the first row to clear |
|
end row |
number |
The number of the last row to clear |
Returns
Returns a boolean value true if the operation was successful
Example
This example clears the contents in rows 13 and 14 of this table on Sheet 1 of the samplefile.
The workflow below clears the range of rows from 13 to 14 (inclusive). The result is stored in the boolean variable boolean_a.
When executed, the specified range of rows are cleared.
The variable boolean_a is set to true, indicating that the operation was successful.
Delete Excel Rows
Removes the specified range of rows from the specified sheet in the specified Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
start row |
number |
The number of the first row to clear |
|
end row |
number |
The number of the last row to clear |
Returns
Returns a boolean value true if the operation was successful
Example
This example removes the contents in rows 13 and 14 of this table on Sheet 1 of the sample file.
The workflow below removes the range of rows from 13 to 14 (inclusive). The result is stored in the boolean variable boolean_a.
When executed, the specified range of rows are romoved.
The variable boolean_a is set to true, indicating that the operation was successful.
Export DataTable to CSV
Exports an existing DataTable to a CSV file. See DataTable Library Object.
Function Structure
Export DataTable <DataTable> to CSV with <FileName> and <Delimiter>
Returns
Returns a boolean value true if the operation was successful
Export DataTable to Excel
Exports an existing DataTable to a Excel file. See DataTable Library Object.
Function Structure
Export DataTable <DataTable> to Excel with <FileName> having <SheetName>
Returns
Returns a boolean value true if the operation was successful
Get Excel Cell Value
Retrieves the value of a specified cell in a specified sheet in an Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet Name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The address of the cell, for example, A1 |
Returns
Returns the value of the specified cell as text
Example
This example retrieves the value of the cell F16 from the file at c:/Temp/SampleFile.xlsx.
The cell to retrieve is shown below:
The workflow below retrieves the value of cell F16 and stores it in the text variable Excel_Cell_Value:
In the monitor, the value of Excel_Cell_Value is set to 738.68.
Get Excel Column Count
Counts the number of columns in the range starting at the first non-empty column and ending at the last non-empty column, inclusive, in the specified sheet in the specified Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
ignore empty columns |
boolean | Set to True to exclude empty columns from the column count, or to Falseto include them. |
|
columns have headers |
boolean |
Set to True if the table has headers, or Falseif it does not. If set to True and ignore empty columns is set to True , then an empty column will be excluded from the column count even though it has a value in the header row. |
Returns
Returns a number indicating the number of columns on the specified sheet.
Example
Sheet 3 of the sample file includes this table. Note that the table has headers. Column D is empty (apart from its header).
The workflow below counts the number of columns on the sheet. Ignore empty columns is set to True, so empty columns will not be included in the count. The result is stored in the number variable number.
When executed, the value of the variable number is set to 6, as there are 6 non-empty columns.
The workflow below is modified so that Ignore empty columns is set to False, so empty columns will be included in the count.
When executed, the value of the variable number is set to 7, as there are 7 columns including empty columns.
Get Table Range in Excel
Retrieves the table range of an Excel table with a specified name.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
name of table |
text |
The name of the table in the Excel file |
Returns
Returns the range as text, in the format A1:C5.
Example
This example retrieves the table range of the table named SaleItems from the file at c:/Temp/SampleFile.xlsx.
The table is shown below. Note that it spans cells B11 to F16.
The workflow below retrieves the range of the table and stores it in the variable table_range_response.
In the monitor, the value of table_range_response is set to B11:F16.
Import CSV to DataTable
Imports a DataTable from a CSV file. See DataTable Library Object.
Function Structure
Import CSV with <FileName>, <Delimiter> and <ColumnHeader> to DataTable
Returns
Returns DataTable
Import Excel to DataTable
Imports a DataTable from an Excel file.
If datatable contains a header, select Column Header as True.
Range is cell range in the excel sheet where datatable is present. If Range is empty, datatable in the cell range is detected automatically. For Example: If datatable is present in cell range A2 to F8, then the Range is A2:F8.
Function Structure
Import Excel with <FileName>, <SheetName>, <Range> and <ColumnHeader> to DataTable
Returns
Returns DataTable
Insert Row to Excel Sheet
Inserts a new row on a specified sheet in a specified Excel file below a specified row.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
|
after row |
number |
The number of the row under which to insert the new row |
Returns
Returns a number indicating the row number of the inserted row.
Example
This workflow inserts a new row below row 14 on Sheet 1 of the sample file. The result is stored in the number variable number.
When executed, the new row is inserted in the Excel file.
The variable number is set to 15 because the new row is at row 15.
Read Column Excel Sheet
Retrieves the values of all cells in a single column in a specified sheet in an Excel file, from a specified cell downwards.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet Name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The address of the top cell of the column, specified in the format A1. If set to A5, for example, will return all values in column A from row 5 downward. |
Returns
Returns the column's cell values in a text list
Example
This example retrieves all values in column F from row 12 downward, as shown below.
The workflow below retrieves the values and stores them in the text list get_excel_col_vals_response.
In the monitor, the values of the cells are stored in the text list get_excel_col_vals_response.
Read Excel Cell Formula
Retrieves the formula in a specified cell in a specified sheet in an Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The address of the cell, in format A1 |
Returns
Returns the cell formula as text
Example
This example retrieves the formula in cell F16, shown below.
The workflow below retrieves the formula and stores it in the text variable Get_Excel_Cell_Formula.
In the monitor, the variable Get_Excel_Cell_Formula is set to the formula in the specified cell.
Read Range From Excel Sheet
Retrieves the values of all cells in a specified range in a specified sheet in an Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet Name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The cell range, specified in format A1:C15 |
Returns
Returns a list of rows containing the values of the cells in the range
Example
This example retrieves the contents of the cells in the range B12 to F14 from the Excel file at c:/Temp/SampleFile.xlsx.
The workflow retrieves the values and stores them in the list of rows get_excel_range_values_response.
In the monitor, the list of rows get_excel_range_values_response is set to the values of the cells in the specified range.
Read Row From Excel Sheet
Retrieves the values of all cells in a single row in a specified sheet in an Excel file, from a specified cell rightwards.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet Name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The address of the left-most cell of the row, specified in the format C1. If set to C5, for example, will return all values in row 5 from column C rightward. |
Returns
Returns a list of text containing the cell values
Example
This example retrieves all values in row 12 from column B rightward, as shown below.
The workflow below retrieves the values and stores them in the text list get_excel_row_values_response.
In the monitor, the values of the cells are stored in the text list get_excel_row_values_response.
Return First Empty Row
Retrieves the row number of the first empty row in the specified sheet in the specified Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
Returns
Returns the row number as a number.
Example
The sample file includes the following table on Sheet 3.
The workflow below retrieves the row number of the first empty row on this sheet and stores it in the number variable number.
When executed, the workflow sets number to 6. Row 6 is the first empty row in the sheet, although there are populated rows below it.
Return Last Empty Row
Retrieves the row number of the row below the last populated row in the specified sheet in the specified Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
full path |
text |
The full path (including file name) of the Excel file |
|
sheet name |
text |
The name of the sheet in the Excel file |
Returns
Returns the row number as a number.
Example
The sample file includes the following table on Sheet 3.
The workflow below retrieves the row number of the first empty row below the last populated row on this sheet and stores it in the number variable number.
When executed, the workflow sets Number to 8. Since row 7 is the last populated row on the sheet, the workflow returned the row number of the row below that.
Set Excel Cell Value
Sets the value of a specified cell on a specified sheet in an Excel file.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet name |
text |
The name of the sheet in the Excel file |
|
Address |
text |
The address of the cell, specified in format A1 |
Returns
Returns a boolean value true if the operation was successful.
Example
In this example, the cell at B8 is set to Email, and the cell at C8 is set to accounts@royal-service.com. The boolean variables Set_Excel_Cell_Value and Set_Excel_Cell_Value_2 are used to indicate success or failure.
The cells are populated as expected.
Write Range In Excel
Sets the values of all cells in a specified range on a specified sheet in an Excel file, and sets the data format for each column in the range.
The values provided must match the formats specified or the operation will fail.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet name |
text |
The name of the sheet in the Excel file |
|
Range |
text |
The cell range, specified in format A1:C15 If the size of the range does not match the size of the data supplied, the range will be ignored and the data supplied will be written starting from the top left cell of the specified range. |
|
Column Types |
list of rows |
The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used. |
|
Rows |
list of rows |
The values to write to the cells in the range |
Returns
Returns a boolean value true if the operation was successful.
Example
This example adds multiple rows to Sheet2 in the file at c:/Temp/SampleFile.xlsx. The data is as follows:
| A | B | C | |
|
Format |
datetime | string | decimal |
|
Row 1 |
1 January 2020 | Purchase | 4131.21 |
| Row 2 | 1 February 2020 | Refund | -50.32 |
The workflow below populates the following rows and lists of rows:
-
data_formats_row_b: Holds the format values as per the table above. This row is added to the list of rows data_formats_b.
-
data_row_b, data_row_c: Hold the data values in the rows as per the table above. The rows are added to the list of rows data_b.
The rows are then written to the Excel file. The boolean variable boolean_d is set to indicated success or failure.
After execution, the rows are added to the Excel file.
The boolean value boolean_d is set to true, indicating success.
Write Range In Excel With Headers
Sets the values of all cells in a specified range on a specified sheet in an Excel file, sets the data format for each column in the range, and defines range column headers.
The values provided must match the formats specified or the operation will fail.
Parameters
|
Parameter |
Input Type |
Description |
|---|---|---|
|
File Name |
text |
The full path (including file name) of the Excel file |
|
Sheet Name |
text |
The name of the sheet in the Excel file |
|
Range |
text |
The cell range, specified in format A1:C15 If the size of the range does not match the size of the data supplied, the range will be ignored and the data supplied will be written starting from the top left cell of the specified range. |
|
Column Types |
list of rows |
The data formats to apply to each column in the range Valid data formats are: datetime, decimal, number, string, bool. Note: Although this parameter is specified using a list of rows, only the first row will be used. |
|
Rows |
list of rows |
The values to write to the cells in the range |
|
Column Headers |
list of rows |
The headers to apply to the columns in the range Note: Although this parameter is specified as a list of rows, only the contents of the first row will be used for headers, the other rows will be ignored. |
Returns
Returns a boolean value true if the operation was successful
Example
This example adds a row of data with headings to Sheet3 in the file at c:/Temp/SampleFile.xlsx. The data is as follows:
| A | B | C | |
|
Format |
datetime | string | decimal |
| Heading | Date | Event | Amount |
|
Row 1 |
1 January 2020 | Purchase | 4131.21 |
The workflow below populates the following rows and lists of rows:
-
data_formats_row_d: Holds the format values as per the table above. This row is added to the list of rows data_formats_d.
-
data_headings_row: Holds the row headings as per the table above. The row is added to the list of rows data_headings.
-
data_row_e: Holds the data values in the row as per the table above. The row is added to the list of rows data_e.
The heading and row is then written to the Excel file. The boolean variable boolean_e is set to indicated success or failure.
After execution, the data is written to the Excel file.
The boolean value boolean_e is set to true, indicating success.
